﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Security.Cryptography;
using Shuba.Model;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace Shuba.Data
{
    public class MobileFile
    {
        public List<MobileFileInfo> GetList(string keyword)
        {
            List<MobileFileInfo> result = new List<MobileFileInfo>();
            Database db = DBConnection.CreateDatabase();
            
            string sql = @"SELECT DATE_FILE_UPLOAD,DATE_FILE_MODIFIED,FILE_SIZE_UPLOAD,
                            PATH_FILE_UPLOAD,NAME_FOLDER_UPLOAD,FILE_NAME_UPLOAD,[VERSION],[CHECKSUM]
                            FROM TBL_MS_MOBILE_FILE_VERSION WHERE FILE_NAME_UPLOAD LIKE @SEARCH OR [VERSION] LIKE @SEARCH";

            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@SEARCH", DbType.String, "%" + keyword + "%");

            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    result.Add(Create(reader));
                }
                reader.Close();
            }
            return result;
        }

        public MobileFileInfo Get(string version)
        {
            MobileFileInfo info = null;
            Database db = DBConnection.CreateDatabase();

            string sql = @"SELECT * FROM TBL_MS_MOBILE_FILE_VERSION WHERE [VERSION] LIKE @VERSION";

            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@VERSION", DbType.String, version);

            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    info = Create(reader);
                    info.EntryBy = reader.GetString(reader.GetOrdinal("ENTRY_BY"));
                    info.EntryDate = reader.GetDateTime(reader.GetOrdinal("ENTRY_DATE"));
                    info.UpdateBy = reader.GetString(reader.GetOrdinal("UPDATE_BY"));
                    info.UpdateDate = reader.GetDateTime(reader.GetOrdinal("UPDATE_DATE"));
                }
                reader.Close();
            }
            return info;
        }

        public void Save(MobileFileInfo info)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                    UPDATE TBL_MS_MOBILE_FILE_VERSION_TMP
                       SET DATE_FILE_MODIFIED = @DATE_FILE_MODIFIED
                          ,FILE_SIZE_UPLOAD = @FILE_SIZE_UPLOAD
                          ,PATH_FILE_UPLOAD = @PATH_FILE_UPLOAD
                          ,NAME_FOLDER_UPLOAD = @NAME_FOLDER_UPLOAD
                          ,FILE_NAME_UPLOAD = @FILE_NAME_UPLOAD      
                          ,UPDATE_BY = @UPDATE_BY
                          ,UPDATE_DATE = @UPDATE_DATE      
                          ,[CHECKSUM] = @CHECKSUM
                          ,ACT_TYPE = @ACT_TYPE
                     WHERE [VERSION] = @VERSION
                     IF @@ROWCOUNT = 0
                     INSERT INTO TBL_MS_MOBILE_FILE_VERSION_TMP
                               (DATE_FILE_UPLOAD
                               ,DATE_FILE_MODIFIED
                               ,FILE_SIZE_UPLOAD
                               ,PATH_FILE_UPLOAD
                               ,NAME_FOLDER_UPLOAD
                               ,FILE_NAME_UPLOAD
                               ,ENTRY_BY
                               ,ENTRY_DATE
                               ,UPDATE_BY
                               ,UPDATE_DATE
                               ,[VERSION]
                               ,[CHECKSUM]
                               ,ACT_TYPE)
                         VALUES
                               (@DATE_FILE_UPLOAD
                               ,@DATE_FILE_MODIFIED
                               ,@FILE_SIZE_UPLOAD
                               ,@PATH_FILE_UPLOAD
                               ,@NAME_FOLDER_UPLOAD
                               ,@FILE_NAME_UPLOAD
                               ,@ENTRY_BY
                               ,@ENTRY_DATE
                               ,@UPDATE_BY
                               ,@UPDATE_DATE
                               ,@VERSION
                               ,@CHECKSUM
                               ,@ACT_TYPE)");

            db.AddInParameter(cmd, "@DATE_FILE_UPLOAD", DbType.DateTime, DateTime.Now);
            db.AddInParameter(cmd, "@DATE_FILE_MODIFIED", DbType.DateTime, DateTime.Now);
            db.AddInParameter(cmd, "@FILE_SIZE_UPLOAD", DbType.Int64, info.FileSizeUpload);
            db.AddInParameter(cmd, "@PATH_FILE_UPLOAD", DbType.String, info.PathFileUpload);
            db.AddInParameter(cmd, "@NAME_FOLDER_UPLOAD", DbType.String, info.NameFolderUpload);
            db.AddInParameter(cmd, "@FILE_NAME_UPLOAD", DbType.String, info.FileNameUpload);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, info.EntryBy);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, info.EntryDate);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, info.UpdateBy);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, info.UpdateDate);
            db.AddInParameter(cmd, "@VERSION", DbType.String, info.Version);
            db.AddInParameter(cmd, "@CHECKSUM", DbType.Int64, info.Checksum);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, info.ActType);
            db.ExecuteNonQuery(cmd);
        }

        /*
        public void Delete(string version)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_MS_MOBILE_FILE_VERSION_TMP WHERE [VERSION] = @VERSION");

            db.AddInParameter(cmd, "@VERSION", DbType.String, version);            
            db.ExecuteNonQuery(cmd);
        }*/

        private MobileFileInfo Create(IDataReader dr)
        {
            MobileFileInfo info = new MobileFileInfo();
            info.DateFileUpload = dr.GetDateTime(dr.GetOrdinal("DATE_FILE_UPLOAD"));
            info.DateFileModified = dr.GetDateTime(dr.GetOrdinal("DATE_FILE_MODIFIED"));
            info.FileSizeUpload = dr.GetInt64(dr.GetOrdinal("FILE_SIZE_UPLOAD"));
            info.PathFileUpload = dr.GetString(dr.GetOrdinal("PATH_FILE_UPLOAD"));
            info.NameFolderUpload = dr.GetString(dr.GetOrdinal("NAME_FOLDER_UPLOAD"));
            info.FileNameUpload = dr.GetString(dr.GetOrdinal("FILE_NAME_UPLOAD"));            
            info.Version = dr.GetString(dr.GetOrdinal("VERSION"));
            info.Checksum = dr.GetInt64(dr.GetOrdinal("CHECKSUM"));
            return info;
        }

        public List<MobileFileInfo> GetListApproval(string UPDATE_BY)
        {
            List<MobileFileInfo> result = new List<MobileFileInfo>();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT DATE_FILE_UPLOAD,DATE_FILE_MODIFIED,FILE_SIZE_UPLOAD,
                            PATH_FILE_UPLOAD,NAME_FOLDER_UPLOAD,FILE_NAME_UPLOAD,[VERSION],[CHECKSUM],ACT_TYPE,UPDATE_BY,UPDATE_DATE
                            FROM TBL_MS_MOBILE_FILE_VERSION_TMP WHERE UPDATE_BY <> @UPDATE_BY";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@UPDATE_BY", DbType.String, UPDATE_BY);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    MobileFileInfo info = Create(mydr);                   
                    info.ActType = mydr.GetString(mydr.GetOrdinal("ACT_TYPE"));
                    info.UpdateBy = mydr.GetString(mydr.GetOrdinal("UPDATE_BY"));
                    info.UpdateDate = mydr.GetDateTime(mydr.GetOrdinal("UPDATE_DATE"));
                    result.Add(info);
                }
                mydr.Close();
            }
            return result;
        }

        public void Approve(string version, string ACT_TYPE)
        {
            Database mydb = DBConnection.CreateDatabase();
            DbCommand mycomm = mydb.GetStoredProcCommand("APPROVAL_MOBILE_FILE_VERSION");
            mydb.AddInParameter(mycomm, "@VERSION", DbType.String, version);
            mydb.AddInParameter(mycomm, "@ACT_TYPE", DbType.String, ACT_TYPE);

            mydb.ExecuteNonQuery(mycomm);
        }

        public void Reject(string version)
        {
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"DELETE FROM TBL_MS_MOBILE_FILE_VERSION_TMP
                             WHERE VERSION = @VERSION";
            DbCommand mycmd = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycmd, "@VERSION", DbType.String, version);

            mydb.ExecuteNonQuery(mycmd);
        }

        public MobileFileInfo GetApprovalByID(string version)
        {
            MobileFileInfo info = new MobileFileInfo();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT * FROM TBL_MS_MOBILE_FILE_VERSION_TMP WHERE [VERSION] LIKE @VERSION";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@VERSION", DbType.String, version);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    info = Create(mydr);
                    info.ActType = mydr.GetString(mydr.GetOrdinal("ACT_TYPE"));
                    info.EntryBy = mydr.GetString(mydr.GetOrdinal("ENTRY_BY"));
                    info.EntryDate = mydr.GetDateTime(mydr.GetOrdinal("ENTRY_DATE"));
                    info.UpdateBy = mydr.GetString(mydr.GetOrdinal("UPDATE_BY"));
                    info.UpdateDate = mydr.GetDateTime(mydr.GetOrdinal("UPDATE_DATE"));
                }
                mydr.Close();
            }
            return info;
        }

        
        /*
        public string GenerateCheckSum(string path)
        {
            using (FileStream fs = new FileStream(path, FileMode.Open))
            {
                using (SHA1Managed sha1 = new SHA1Managed())
                {
                    byte[] hash = sha1.ComputeHash(fs);
                    StringBuilder formatted = new StringBuilder(2 * hash.Length);
                    foreach (byte b in hash)
                    {
                        formatted.AppendFormat("{0:X2}", b);
                    }                    
                    return formatted.ToString();
                }
            }
        }*/
    }
}
